"""
cron: 0 */4 * * *
new Env('小说排行榜采集');
"""
import requests
import os
import mysql.connector
from datetime import datetime

# 获取环境变量
host = os.environ.get('HOST')
user = os.environ.get('USER')
password = os.environ.get('PASSWORD')
database = os.environ.get('DATABASE')

print("正在获取环境变量...")
print(f"数据库地址：{host}")
print(f"数据库用户名：{user}")
print(f"数据库密码：{password}")
print(f"数据库名称：{database}")

# 发送HTTP请求获取小说排行榜
print("发送HTTP请求获取小说排行榜数据...")
headers = {
    'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.4472.124 Safari/537.36'
}

# 连接数据库
connection = mysql.connector.connect(
    host=host,
    user=user,
    password=password,
    database=database
)

cursor = connection.cursor()

# 创建表（如果不存在）
create_table_query = """
CREATE TABLE IF NOT EXISTS ningm_novel_rank (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    author VARCHAR(100),
    category VARCHAR(50),
    word_count INT,
    platform VARCHAR(50),
    rank_type VARCHAR(50),
    rank_value INT,
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_platform (platform),
    INDEX idx_rank_type (rank_type),
    INDEX idx_create_time (create_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
"""
cursor.execute(create_table_query)

# 删除旧数据（保留1天内的数据）
print("删除旧数据...")
delete_query = "DELETE FROM ningm_novel_rank WHERE create_time < DATE_SUB(NOW(), INTERVAL 1 DAY)"
cursor.execute(delete_query)

# 获取起点中文网排行榜
def get_qidian_rank():
    try:
        url = 'https://www.qidian.com/rank/hotsales/'
        response = requests.get(url, headers=headers)
        
        if response.status_code == 200:
            # 解析HTML获取排行榜数据
            # 这里需要根据实际HTML结构进行解析
            # 示例数据
            novels = [
                {
                    'title': '斗破苍穹',
                    'author': '天蚕土豆',
                    'category': '玄幻',
                    'word_count': 1000000,
                    'rank': 1
                }
            ]
            
            for novel in novels:
                print(f"正在保存小说数据: {novel['title']}")
                insert_query = """
                    INSERT INTO ningm_novel_rank 
                    (title, author, category, word_count, platform, rank_type, rank_value) 
                    VALUES (%s, %s, %s, %s, %s, %s, %s)
                """
                insert_values = (
                    novel['title'],
                    novel['author'],
                    novel['category'],
                    novel['word_count'],
                    'qidian',
                    'hotsales',
                    novel['rank']
                )
                cursor.execute(insert_query, insert_values)
                
    except Exception as e:
        print(f"获取起点排行榜失败: {str(e)}")

# 获取纵横中文网排行榜
def get_zongheng_rank():
    try:
        url = 'http://www.zongheng.com/rank'
        response = requests.get(url, headers=headers)
        
        if response.status_code == 200:
            # 解析HTML获取排行榜数据
            # 这里需要根据实际HTML结构进行解析
            # 示例数据
            novels = [
                {
                    'title': '诡秘之主',
                    'author': '爱潜水的乌贼',
                    'category': '玄幻',
                    'word_count': 800000,
                    'rank': 1
                }
            ]
            
            for novel in novels:
                print(f"正在保存小说数据: {novel['title']}")
                insert_query = """
                    INSERT INTO ningm_novel_rank 
                    (title, author, category, word_count, platform, rank_type, rank_value) 
                    VALUES (%s, %s, %s, %s, %s, %s, %s)
                """
                insert_values = (
                    novel['title'],
                    novel['author'],
                    novel['category'],
                    novel['word_count'],
                    'zongheng',
                    'popular',
                    novel['rank']
                )
                cursor.execute(insert_query, insert_values)
                
    except Exception as e:
        print(f"获取纵横排行榜失败: {str(e)}")

# 获取并保存排行榜数据
print("开始获取小说排行榜数据...")
get_qidian_rank()
get_zongheng_rank()

connection.commit()
cursor.close()
connection.close()

print("数据保存完成！") 